RDS(PostgreSQL)とRedshiftに於けるデータ移動の簡易メモ

しんや

2015.06.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

小ネタです。まとめって程でも無いので"簡易メモ"としました。

RDS(PostgreSQL)に於けるデータのインポートやエクスポート、またはRDS(PostgreSQL)とAmazon Redshift間に於けるデータ連携について情報を整理しておく機会がありましたので、簡単にではありますが当エントリにその内容をまとめてみました。

RDS(PostgreSQL)とAmazon Redshiftの関係性

以下ドキュメントに記載があるように、Amazon RedshiftはPostgreSQL8.0.2に準拠した形になっています。PostgreSQLで使っていた大抵のSQL文は利用出来ますが、場合によっては使えなかったり、使い方が異なっていたりしますので注意が必要です。

Amazon Redshift および PostgreSQL - Amazon Redshift

RDS(PostgreSQL)とAmazon RedshiftのCOPY処理

データをある場所から別の場所へ移動する"COPY"処理はどちらにも用意されています。文法も似ていますが微妙な部分で仕様や実装が異なっています。

PostgreSQL：COPY
Amazon Redshift：COPY - Amazon Redshift

ローカル環境 or EC2 → RDS(PostgreSQL)

dumpファイル

オンプレのPostgreSQLからdumpファイルをエクスポートする場合は、この経路でそのままRDS(PostgreSQL)にデータを移行出来そうです。EC2にdumpファイルを配備した状態であれば、同様にEC2経由でRDS(PostgreSQL)にデータを取り込む事が出来ます。

CSVファイル

毎度お馴染みTableauのSuperstoreサンプルからOrdersテーブルデータをチョイス。ディレクトリ直下にある状態でpsqlコマンドにてログインし、

$ ls superstore-orders-utf8.csv 
superstore-orders-utf8.csv
$ psql -h xyz.abc.ap-northeast-1.rds.amazonaws.com -U root -d testdb -p 5432
Password for user root: 
psql (9.4.1)
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

=>

Amazon Redshiftで試した時に使ったCREATE TABLE文をそのまま使い、テーブル作成、そしてデータ投入。PostgreSQLの場合は\COPYコマンドを使ってデータをクライアントから投入する事が可能です。ヘッダ行がある場合はHEADERオプションで対応可能です。

=> CREATE TABLE public.orders (
  order_id INT NOT NULL,
  order_date DATE NOT NULL,
  priority VARCHAR(12) NOT NULL,
  quantity SMALLINT NOT NULL,
  sales DOUBLE PRECISION,
  discount_rate DOUBLE PRECISION,
  ship_mode VARCHAR(20) NOT NULL,
  profit INT NOT NULL,
  unit_price INT NOT NULL,
  ad_expenses INT NOT NULL,
  shipping_cost INT NOT NULL,
  customer_name VARCHAR(50) NOT NULL,
  prefecture VARCHAR(12) NOT NULL,
  city VARCHAR(20) NOT NULL,
  area VARCHAR(12) NOT NULL,
  shop_name VARCHAR(20) NOT NULL,
  customer_segment VARCHAR(30) NOT NULL,
  product_category VARCHAR(30) NOT NULL,
  product_sub_category VARCHAR(100) NOT NULL,
  product_id VARCHAR(10) NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  product_description VARCHAR(200) NOT NULL,
  product_container VARCHAR(100) NOT NULL,
  base_margin DOUBLE PRECISION,
  supplier VARCHAR(30) NOT NULL,
  deliver_date DATE NOT NULL,
  ship_date DATE NOT NULL
);
CREATE TABLE
=> 
=> \timing
Timing is on.
=> \COPY public.orders FROM 'superstore-orders-utf8.csv' WITH CSV HEADER;
COPY 8369
Time: 9537.818 ms
=> 
=> SELECT COUNT(*) FROM public.orders;
 count 
-------
  8369
(1 row)

Time: 108.476 ms
=>

S3 → RDS(PostgreSQL)

では、Amazon Redshiftで実践するような流れ、Amazon S3に一旦データをアップロードし、S3にあるデータをRDS(PostgreSQL)に投入する術はあるのでしょうか？

...と、幾つか調べてみましたが、どうやらこの経路で投入する手段はどうやら無さそう。ダンプファイルでは無い、別のシステムからエクスポートされたCSVデータの場合は上記記載の様に\COPYコマンドを使う方法でクライアント端末、もしくはEC2インスタンスからデータを取り込む事になりそうです。

amazon s3 - how to import data files from s3 to postgresql rds - Stack Overflow

RDS(PostgreSQL)→S3

S3からのRDS(PostgreSQL)へのインポートが出来ないのと同様に、RDS(PostgreSQL)からS3へのエクスポートも手段は用意されていないようです。

RDS(PostgreSQL) → EC2/Local

RDS(PostgreSQL)からのdumpエクスポートはRedshiftへのdumpインポート手段が無いので割愛。dumpエクスポートに関しては以下のエントリを参考にしてみると良さそうです。

RDSにPostgreSQLのdumpをインポートする - Qiita

RDS(PostgreSQL)からCSVをエクスポートするにはインポート同様、\COPYコマンドでデータを抽出する事が可能となっています。

CSVファイルのインポート、エクスポート - toshifusaの日記

=> \COPY public.orders TO '/Users/xxxxxxxxxxx/Desktop/ss-orders.csv' (DELIMITER ',');

まとめ

以上の事から、RDS(PostgreSQL)に於けるデータのインポート・エクスポートについてはAmazon Redshiftで行うようなS3経由でのアクセスは行えず、一度sqlコマンド(psqlによる\COPYコマンド)を実行出来る環境(AWS上であればEC2等)を介する必要がありそうです。下記構成図はRDS(PostgreSQL)のデータインポート・エクスポートの流れを図にしてみたものですが、EC2からの\COPYコマンドでRDS(PostgreSQL)のデータを手元(EC2サーバ配下)に落とした後、すぐにS3のコマンドでS3にアップロード、アップロード完了を以ってEC2サーバ内のデータを削除、みたいな感じにすればひとまずはRedshiftへデータを繋げられそうです。幸いCSVの形式については同じ内容でそのまま移行出来ますので、検証及び移行の際にはスムーズな作業を心掛けたいものですね。こちらからは以上です。